﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
//using System.Linq;
using System.Text;
using System.Windows.Forms;
using MyLib;
using System.Collections;
using System.IO;

namespace ImportSubscriber
{
    public partial class frmMain : Form
    {
        #region Others

        //public const string CONFIG_CONNECTSTRING = "ConnectString";

        public frmMain()
        {
            InitializeComponent();
        }

        private void frmMain_Load(object sender, EventArgs e)
        {
            this.Text = String.Format("{0} {1}({2})", Common.ProductName, Common.UpdateLog[0].version, Common.UpdateLog[0].date);
            Log.Write("{0} started.", Common.ProductName);

            tb_file.Text = Properties.Settings.Default.LastFilename;
            ClearResult();
            this.btn_preview.Enabled = true;
            this.btn_import.Enabled = false;
            this.btn_stop.Enabled = false;
        }

        public void WriteLogImplement(Color color, string s, bool log_to_window, bool log_to_file)
        {
            try
            {
                if (tbLog.TextLength > 100000)
                {
                    tbLog.SelectionStart = 0;
                    tbLog.SelectionLength = 50000;
                    tbLog.SelectedText = "";
                }

                if (log_to_window)
                {
                    string dt = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss: ");
                    int start = tbLog.TextLength;
                    tbLog.AppendText(dt);
                    tbLog.SelectionStart = start;
                    tbLog.SelectionLength = dt.Length;
                    tbLog.SelectionColor = Color.Blue;

                    start = tbLog.TextLength;
                    tbLog.AppendText(s + "\r\n");
                    tbLog.SelectionStart = start;
                    tbLog.SelectionLength = s.Length;
                    tbLog.SelectionColor = color;

                    tbLog.SelectionStart = tbLog.TextLength;
                    tbLog.SelectionLength = 0;
                    tbLog.ScrollToCaret();
                }

                if (log_to_file)
                {
                    string log_filename = Utility.GetCurrentPath() + "Log_" + DateTime.Now.ToString("yyyy_MM_dd") + ".txt";
                    FileStream fs = new System.IO.FileStream(log_filename, FileMode.Append);
                    byte[] buffer = System.Text.ASCIIEncoding.ASCII.GetBytes(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss: ") + s + "\r\n");
                    fs.Write(buffer, 0, buffer.Length);
                    fs.Flush();
                    fs.Close();
                }
                //Application.DoEvents();
            }
            catch
            {
            }
        }

        void ClearResult()
        {
            lbl_organization.Text = "New organization";
            lbl_subscriber.Text = "New subscriber";
            dgv_organization.Rows.Clear();
            dgv_subscriber.Rows.Clear();
        }

        private void btn_select_file_Click(object sender, EventArgs e)
        {
            OpenFileDialog op = new OpenFileDialog();
            //op.InitialDirectory = @"c:\";
            op.FileName = Path.GetFileName(tb_file.Text);
            op.RestoreDirectory = true;
            op.Filter = "All|*.*|Excel|*.xls;*.xlsx|CSV|*.csv|Text|*.txt";
            if (op.ShowDialog() != DialogResult.OK)
                return;
            tb_file.Text = op.FileName;
        }

        public class Organization
        {
            public string org;
            public string org_fullname;
            public string site;
            public string costcentre;

            public Organization(string org, string org_fullname, string site, string costcentre)
            {
                this.org = org;
                this.org_fullname = org_fullname;
                this.site = site;
                this.costcentre = costcentre;
            }
        }

        public class Subscriber
        {
            public string S_No;
            public string OrgID;
            public string OrgFullname;
            public string SiteID;
            public string Costcentre;
            public string User_Name;
            public string Service_Type;
            public string Monthly_OneTime;
            public string Subscription_Start_Date;
            public string Subscription_End_Date;
            public string New_Existing_Terminated;
            public string AssetID;
            public string Hostname;
            public string Remarks;
        }

        const string SheetName = "Plan B1";

        void ValidExcelFile(MyExcel excel, ref int start_row)
        {
            //string s = "";
            //for (int i = 1; i <= 14; i++)
            //    s += "'" + excel.ReadString(3, i) + "',";
            //'S/No' '* Agency Short Name' '* Agency Full Name' '* Site' '* Cost Centre' '* User Name' '* Service Type' '* Monthly / One-Time' 'Subscription Start Date' 'New/Existing/Terminated' 'Asset ID' 'Hostname' 'Subscription End Date' 'New/Existing/Terminated'

            int title_row = 0;
            for(int i = 1; i <= 10; i++)
                if (excel.ReadString(i, 1).IndexOf("S/No") >= 0)
                {
                    title_row = i;
                    break;
                }
            if (title_row == 0) throw new Exception("can not find title line");

            int n = 1;
            if (excel.ReadString(title_row, n).IndexOf("S/No") < 0) throw new Exception(String.Format("can not find column 'S/No' at {0}{1}", Index2ColumnName(n), title_row)); n++;
            if (excel.ReadString(title_row, n).IndexOf("Agency Short Name") < 0) throw new Exception(String.Format("can not find column '* Agency Short Name' at {0}{1}", Index2ColumnName(n), title_row)); n++;
            if (excel.ReadString(title_row, n).IndexOf("Agency Full Name") < 0) throw new Exception(String.Format("can not find column '* Agency Full Name' at {0}{1}", Index2ColumnName(n), title_row)); n++;
            if (excel.ReadString(title_row, n).IndexOf("Site") < 0) throw new Exception(String.Format("can not find column '* Site' at {0}{1}", Index2ColumnName(n), title_row)); n++;
            if (excel.ReadString(title_row, n).IndexOf("Cost Centre") < 0) throw new Exception(String.Format("can not find column '* Cost Centre' at {0}{1}", Index2ColumnName(n), title_row)); n++;
            if (excel.ReadString(title_row, n).IndexOf("User Name") < 0) throw new Exception(String.Format("can not find column '* User Name' at {0}{1}", Index2ColumnName(n), title_row)); n++;
            if (excel.ReadString(title_row, n).IndexOf("Service Type") < 0) throw new Exception(String.Format("can not find column '* Service Type' at {0}{1}", Index2ColumnName(n), title_row)); n++;
            if (excel.ReadString(title_row, n).IndexOf("Monthly / One-Time") < 0) throw new Exception(String.Format("can not find column '* Monthly / One-Time' at {0}{1}", Index2ColumnName(n), title_row)); n++;
            if (excel.ReadString(title_row, n).IndexOf("Subscription Start Date") < 0) throw new Exception(String.Format("can not find column 'Subscription Start Date' at {0}{1}", Index2ColumnName(n), title_row)); n++;
            //n++;
            //n++;
            //n++;
            if (excel.ReadString(title_row, n).IndexOf("Subscription End Date") < 0) throw new Exception(String.Format("can not find column 'Subscription End Date' at {0}{1}", Index2ColumnName(n), title_row)); n++;
            if (excel.ReadString(title_row, n).IndexOf("New/Existing/Terminated") < 0) throw new Exception(String.Format("can not find column 'New/Existing/Terminated' at {0}{1}", Index2ColumnName(n), title_row)); n++;
            if (excel.ReadString(title_row, n).IndexOf("Asset ID") < 0) throw new Exception(String.Format("can not find column 'Asset ID' at {0}{1}", Index2ColumnName(n), title_row)); n++;
            if (excel.ReadString(title_row, n).IndexOf("Hostname") < 0) throw new Exception(String.Format("can not find column 'Hostname' at {0}{1}", Index2ColumnName(n), title_row)); n++;
            if (excel.ReadString(title_row, n).IndexOf("Remarks") < 0) throw new Exception(String.Format("can not find column 'Remarks' at {0}{1}", Index2ColumnName(n), title_row)); n++;

            start_row = title_row + 1;
        }

        Subscriber ReadSubscriber(MyExcel excel, int row, ref int n)
        {
            if (row == 194)
                Application.DoEvents();

            if (IsBottom(excel, row))
                return null;

            Subscriber data = new Subscriber();
            data.S_No = excel.ReadString(row, n).Trim(); n++;
            data.OrgID = excel.ReadString(row, n).Trim(); n++;
            data.OrgFullname = excel.ReadString(row, n).Trim(); n++;
            data.SiteID = excel.ReadString(row, n).Trim(); n++;
            data.Costcentre = excel.ReadString(row, n).Trim(); n++;
            data.User_Name = excel.ReadString(row, n).Trim(); n++;
            data.Service_Type = excel.ReadString(row, n).Trim(); n++;
            data.Monthly_OneTime = excel.ReadString(row, n).Trim(); n++;
            data.Subscription_Start_Date = excel.ReadString(row, n).Trim(); n++;
            //n++;
            //n++;
            //n++;
            data.Subscription_End_Date = excel.ReadString(row, n).Trim(); n++;
            data.New_Existing_Terminated = excel.ReadString(row, n).Trim(); n++;
            data.AssetID = excel.ReadString(row, n).Trim(); n++;
            data.Hostname = excel.ReadString(row, n).Trim(); n++;
            data.Remarks = excel.ReadString(row, n).Trim(); n++;
            return data;
        }

        void ValidSubscriber(Subscriber data, DateTime default_start_date)
        {
            if (data.OrgID == "") throw new Exception("Organization is mandatory");
            if (data.OrgFullname == "") throw new Exception("Organization Name is mandatory");
            if (data.SiteID == "") throw new Exception("Site is mandatory");
            if (data.Costcentre == "") throw new Exception("Cost Centre is mandatory");
            if (data.User_Name == "") throw new Exception("User Name is mandatory");
            if (data.Service_Type == "") throw new Exception("Service Type is mandatory");
            //Monthly_OneTime;
            if (data.Subscription_Start_Date == "")
            {
                if (default_start_date == DateTime.MinValue)
                    throw new Exception("Subscription Start Date is mandatory");
                else
                    data.Subscription_Start_Date = default_start_date.ToString("yyyy-MM-dd");
            }
            if (!data.Subscription_Start_Date.IsDateTime()) throw new Exception("Invalid Subscription Start Date");
            //public string Subscription_End_Date;
            //public string New_Existing_Terminated;
            if (data.AssetID == "") throw new Exception("AssetID is mandatory");
            if (data.Hostname == "") data.Hostname = "none"; //throw new Exception("Hostname is mandatory");
            //public string Remarks;
        }

        bool IsBottom(MyExcel excel, int line)
        {
            try
            {
                if ((excel.ReadString(line, 1).Trim() == "") && (excel.ReadString(line + 1, 1).Trim() == ""))
                    return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Search bottom failed! " + ex.Message);
            }
            return false;
        }

        void OnProcess(int progress)
        {
            pb_import.Value = progress > 100 ? 100 : progress;
        }

        private void btn_settings_Click(object sender, EventArgs e)
        {
            frmDatabaseSetting frm = new frmDatabaseSetting();
            frm.ShowDialog();
        }

        private void btn_about_Click(object sender, EventArgs e)
        {
            frmAbout frm = new frmAbout();
            frm.ShowDialog();
        }

        private void btn_close_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        void CreateColumn(DataGridView dgv, string HeaderText, int width)
        {
            int n = dgv.Columns.Add("column_" + HeaderText.Replace(" ", ""), HeaderText);
            dgv.Columns[n].Width = width;
        }

        void AddOrganization(Organization data)
        {
            organizations.Add(data);

            DataGridView dgv = dgv_organization;
            if (dgv.Columns.Count == 0)
            {
                CreateColumn(dgv, "Organisation", 50);
                CreateColumn(dgv, "Organisation Full Name", 50);
                CreateColumn(dgv, "SiteID", 50);
                CreateColumn(dgv, "Cost Centre", 50);
                //CreateColumn(dgv, "Error", 1000);
            }

            dgv.Rows.Add(new object[]{
                data.org,
                data.org_fullname,
                data.site,
                data.costcentre
                //,errormsg
            });

            lbl_organization.Text = String.Format("New organization ({0})", dgv.Rows.Count);
        }

        void AddSubscriber(Subscriber data)
        {
            subscribers.Add(data);

            DataGridView dgv = dgv_subscriber;
            if (dgv.Columns.Count == 0)
            {
                CreateColumn(dgv, "S/No", 50);
                CreateColumn(dgv, "Organisation", 50);
                CreateColumn(dgv, "Organisation Full Name", 50);
                CreateColumn(dgv, "SiteID", 50);
                CreateColumn(dgv, "Cost Centre", 50);
                CreateColumn(dgv, "User Name", 50);
                CreateColumn(dgv, "Service Type", 80);
                CreateColumn(dgv, "Monthly / One-Time", 100);
                CreateColumn(dgv, "Subscription Start Date", 100);
                CreateColumn(dgv, "Subscription End Date", 50);
                CreateColumn(dgv, "New/Existing/Terminated", 70);
                CreateColumn(dgv, "Asset ID", 50);
                CreateColumn(dgv, "Hostname", 50);
                CreateColumn(dgv, "Remarks", 50);
                //CreateColumn(dgv, "Error", 1000);
            }

            dgv.Rows.Add(new object[]{
                    data.S_No,
                    data.OrgID,
                    data.OrgFullname,
                    data.SiteID,
                    data.Costcentre,
                    data.User_Name,
                    data.Service_Type,
                    data.Monthly_OneTime,
                    data.Subscription_Start_Date,
                    data.Subscription_End_Date,
                    data.New_Existing_Terminated,
                    data.AssetID,
                    data.Hostname,
                    data.Remarks
                    //,errormsg
                });

            lbl_subscriber.Text = String.Format("New subscriber ({0})", dgv.Rows.Count);
        }

        void ExportDataGridView(DataGridView dgv, string filename, string[] title)
        {
            OnProcess(0);
            MyExcel excel = new MyExcel();
            excel.Create();

            int line = 1;
            int col = 1;
            foreach (string s in title)
            {
                col = 1;
                string[] ss = s.Split(new char[] { '\t' });
                foreach (string data in ss)
                    excel.WriteString(line, col++, data);
                line++;
                OnProcess(Convert.ToInt32(line * 100.0 / title.Length));
            }

            col = 1;
            foreach (DataGridViewColumn column in dgv.Columns)
                excel.WriteString(line, col++, column.HeaderText);
            line++;

            OnProcess(0);
            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                col = 1;
                foreach (DataGridViewCell cell in dgv.Rows[i].Cells)
                    excel.WriteString(line, col++, cell.Value.ToString());
                line++;
                OnProcess(Convert.ToInt32(i * 100.0 / dgv.Rows.Count));
            }
            excel.SaveAs(filename);
            excel.Close();
            OnProcess(100);
        }

        #endregion

        List<Organization> organizations = new List<Organization>();
        List<Subscriber> subscribers = new List<Subscriber>();
        bool stop = false;

        private void btn_stop_Click(object sender, EventArgs e)
        {
            stop = true;
        }

        private void btn_preview_Click(object sender, EventArgs e)
        {
            bool do_not_check_duplicate_in_DB = cb_do_not_check_duplicate_in_DB.Checked;
            DateTime default_start_date = dtp_default_start_date.Checked ? dtp_default_start_date.Value : DateTime.MinValue;

            MyExcel excel = null;
            try
            {
                this.btn_preview.Enabled = false;
                this.btn_import.Enabled = false;
                this.btn_stop.Enabled = true;
                stop = false;

                organizations.Clear();
                subscribers.Clear();
                ClearResult();
                Application.DoEvents();

                if (Properties.Settings.Default.ConnectionString == "")
                    throw new Exception("please setup database connection string first!");
                if (!File.Exists(tb_file.Text))
                    throw new Exception("file does not exist");
                string filename = tb_file.Text;
                Log.Write("Process file '{0}'", filename);
                Properties.Settings.Default.LastFilename = filename;
                Properties.Settings.Default.Save();

                DBCommands db = new DBCommands(Properties.Settings.Default.ConnectionString);
                db.ExceptionMode = true;

                int start_row = 3;
                excel = new MyExcel(filename, SheetName);
                ValidExcelFile(excel, ref start_row);

                int error = 0;
                OnProcess(0);
                for (int i = start_row; i <= excel.max_rows; i++)
                {
                    OnProcess(Convert.ToInt32(i * 100.0 / excel.max_rows));
                    Application.DoEvents();
                    if (stop)
                        throw new Exception("cancelled by user!");

                    Subscriber data = null;
                    int n = 1;
                    try
                    {
                        data = ReadSubscriber(excel, i, ref n);
                        if (data == null) //end of file
                            break;
                    }
                    catch (Exception ex)
                    {
                        error++;
                        //throw new Exception(string.Format("Cell[{0}{1}]: {2}", Index2ColumnName(n), i, ex.Message));
                        Log.Write(Color.Red, "Cell[{0}{1}]: {2}", Index2ColumnName(n), i, ex.Message);
                    }

                    try
                    {
                        if (data != null) //if read subscriber ok
                        {
                            ValidSubscriber(data, default_start_date);
                            ProcessOrganization(data, db, do_not_check_duplicate_in_DB);
                            ProcessSubscriber(data, db, do_not_check_duplicate_in_DB);
                        }
                    }
                    catch (Exception ex)
                    {
                        error++;
                        //throw new Exception(string.Format("Line[{0}]: {1}", i, ex.Message));
                        Log.Write(Color.Red, "Line[{0}]: {1}", i, ex.Message);
                    }
                }
                OnProcess(100);
                Log.Write(error == 0 ? Color.Blue : Color.Red, "Preview finished. new organization={0} new subscriber={1} error={2}", dgv_organization.Rows.Count, dgv_subscriber.Rows.Count, error);
                this.btn_preview.Enabled = true;
                this.btn_import.Enabled = error == 0;
                this.btn_stop.Enabled = false;
            }
            catch (Exception ex)
            {
                this.btn_preview.Enabled = true;
                this.btn_import.Enabled = false;
                this.btn_stop.Enabled = false;

                Log.Write(Color.Red, ex.Message);
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (excel != null)
                {
                    excel.Close();
                    excel = null;
                }
            }
        }

        string Index2ColumnName(int n)
        {
            string col = "";
            byte n1 = (byte)(n / 26);
            byte n2 = (byte)(n - n1 * 26);
            if (n1 > 0) col = Encoding.ASCII.GetString(new byte[] { (byte)(Encoding.ASCII.GetBytes("A")[0] + n1 - 1) }) + col;
            if (n2 > 0) col = Encoding.ASCII.GetString(new byte[] { (byte)(Encoding.ASCII.GetBytes("A")[0] + n2 - 1) }) + col;
            return col;
        }

        void ProcessOrganization(Subscriber data, DBCommands db, bool do_not_check_duplicate_in_DB)
        {
            bool found = false;
            for (int i = 0; i < organizations.Count; i++)
                if (data.OrgID.IsSameText(organizations[i].org))
                {
                    found = true;
                    break;
                }
            if (!found && !do_not_check_duplicate_in_DB)
            {
                int n = 0;
                db.ExecSqlQueryCount("select * from Organization where OrgIDName = @OrgID", ref n, data.OrgID);
                if (n > 0)
                    found = true;
            }
            if (!found)
                AddOrganization(new Organization(data.OrgID, data.OrgFullname, "", ""));

            found = false;
            for (int i = 0; i < organizations.Count; i++)
                if (data.SiteID.IsSameText(organizations[i].site))
                {
                    found = true;
                    break;
                }
            if (!found && !do_not_check_duplicate_in_DB)
            {
                int n = 0;
                db.ExecSqlQueryCount(@"
                select * from SitesInOrganization
                    inner join Organization on SitesInOrganization.OrgID = Organization.ID
                where SiteIDName=@SiteID and Organization.OrgIDName = @OrgID", ref n, data.SiteID, data.OrgID);
                if (n > 0)
                    found = true;
            }
            if (!found)
                AddOrganization(new Organization(data.OrgID, data.OrgFullname, data.SiteID, ""));

            found = false;
            for (int i = 0; i < organizations.Count; i++)
                if (data.Costcentre.IsSameText(organizations[i].costcentre))
                {
                    found = true;
                    break;
                }
            if (!found && !do_not_check_duplicate_in_DB)
            {
                int n = 0;
                db.ExecSqlQueryCount(@"
                select * from CostCentresInSite
                    inner join SitesInOrganization on CostCentresInSite.SiteID = SitesInOrganization.ID
                where CostCentreIDName=@Costcentre and SitesInOrganization.SiteIDName=@SiteID", ref n, data.Costcentre, data.SiteID);
                if (n > 0)
                    found = true;
            }
            if (!found)
                AddOrganization(new Organization(data.OrgID, data.OrgFullname, data.SiteID, data.Costcentre));
        }

        void ProcessSubscriber(Subscriber data, DBCommands db, bool do_not_check_subscriber_in_DB)
        {
            for (int i = 0; i < subscribers.Count; i++)
                if (data.AssetID.IsSameText(subscribers[i].AssetID))
                    throw new Exception("AssetID '" + data.AssetID + "' exists in this file");

            if (!do_not_check_subscriber_in_DB)
            {
                int n = 0;
                db.ExecSqlQueryCount("select * from Subscriber where AssetID = @AssetID", ref n, data.AssetID);
                if (n > 0)
                    throw new Exception("AssetID '" + data.AssetID + "' exists in database");
            }

            AddSubscriber(data);
        }

        #region Import

        private void btn_import_Click(object sender, EventArgs e)
        {
            DBCommands db = null;
            try
            {
                this.btn_preview.Enabled = false;
                this.btn_import.Enabled = false;
                this.btn_stop.Enabled = true;
                stop = false;

                if (organizations.Count + subscribers.Count == 0)
                    throw new Exception("No data to import");

                db = new DBCommands(Properties.Settings.Default.ConnectionString);
                db.ExceptionMode = true;
                db.BeginTransaction();

                OnProcess(0);
                int counter = 0;
                for (int i = 0; i < organizations.Count; i++)                    
                {
                    counter++;
                    OnProcess(Convert.ToInt32(counter * 100.0 / (organizations.Count + subscribers.Count)));
                    Application.DoEvents();
                    if (stop)
                        throw new Exception("cancelled by user!");
                    ImportOrganization(organizations[i], db);
                }
                for (int i = 0; i < subscribers.Count; i++)
                {
                    counter++;
                    OnProcess(Convert.ToInt32(counter * 100.0 / (organizations.Count + subscribers.Count)));
                    Application.DoEvents();
                    if (stop)
                        throw new Exception("cancelled by user!");
                    ImportSubscriber(subscribers[i], db);
                }
                OnProcess(100);
                db.Commit();
                Log.Write(Color.Blue, "Import finished. organization={0} subscriber={1}", organizations.Count, subscribers.Count);
            }
            catch (Exception ex)
            {
                if (db != null)
                    db.Rollback();
                Log.Write(Color.Red, ex.ExceptionInfo());
                MessageBox.Show(ex.ExceptionInfo());
            }
            finally
            {
                this.btn_preview.Enabled = true;
                this.btn_import.Enabled = true;
                this.btn_stop.Enabled = false;
            }
        }

        void ImportOrganization(Organization org, DBCommands db)
        {
            if (org.site == "" && org.costcentre == "")
            {
                //insert org
                db.Organization.insert(org.org, org.org_fullname, 0, 1, 1, DateTime.Now, 1, DateTime.Now);
            }
            else if (org.costcentre == "")
            {
                //insert site
                DataTable dt= null;
                db.Organization.select(ref dt, "where OrgIDName = @OrgIDName", org.org);
                if (dt == null || dt.Rows.Count == 0)
                    throw new Exception("Can not find organization '" + org.org +"'");
                if (dt.Rows.Count > 1)
                    throw new Exception("there are " + dt.Rows.Count.ToString() + " organizations have same name '" + org.org + "'");
                long org_id = (long)dt.Rows[0]["ID"];
                db.SitesInOrganization.insert((int)org_id, org.site, org.site, 1, 1, DateTime.Now, 1, DateTime.Now);
            }
            else
            {
                //insert cc
                DataTable dt = null;
                db.SitesInOrganization.select(ref dt, "where SiteIDName = @SiteIDName", org.site);
                if (dt == null || dt.Rows.Count == 0)
                    throw new Exception("Can not find site '" + org.site + "'");
                if (dt.Rows.Count > 1)
                    throw new Exception("there are " + dt.Rows.Count.ToString() + " sites have same name '" + org.site + "'");
                long site_id = (long)dt.Rows[0]["ID"];
                db.CostCentresInSite.insert((int)site_id, org.costcentre, org.costcentre, 1, 1, DateTime.Now, 1, DateTime.Now);
            }
        }

        void ImportSubscriber(Subscriber sub, DBCommands db)
        {
            DataTable dt = null;
            db.CostCentresInSite.select(ref dt, "where CostCentreIDName = @CostCentreIDName", sub.Costcentre);
            if (dt == null || dt.Rows.Count == 0)
                throw new Exception("Can not find cost centre '" + sub.Costcentre + "'");
            if (dt.Rows.Count > 1)
                throw new Exception("there are " + dt.Rows.Count.ToString() + " cost centre have same name '" + sub.Costcentre + "'");
            long cc_id = (long)dt.Rows[0]["ID"];

            if (sub.Subscription_End_Date != "")
                db.Subscriber.insert(
                    0, 0,
                    DateTime.Parse(sub.Subscription_Start_Date),
                    DateTime.Parse(sub.Subscription_End_Date),
                    1, 1, DateTime.Now, 1, DateTime.Now,
                    (int)cc_id, "",
                    sub.User_Name, sub.Service_Type, sub.AssetID, sub.Hostname, "", 1);
            else
                db.ExecSqlCmd(@"
					insert into Subscriber(
						ServiceFormID,
						MasterProfileID,
						EffectiveDate,
						CreatedBy,
						CreatedDatetime,
						ModifiedBy,
						ModifiedDateTime,
						CostCentreID,
						Email,
						Username,
						ServiceType,
						AssetID,
						Hostname,
						OS,
						Status
						)
					values(
						@ServiceFormID,
						@MasterProfileID,
						@EffectiveDate,
						@CreatedBy,
						@CreatedDatetime,
						@ModifiedBy,
						@ModifiedDateTime,
						@CostCentreID,
						@Email,
						@Username,
						@ServiceType,
						@AssetID,
						@Hostname,
						@OS,
						@Status
						)
					", 0, 0,
                    DateTime.Parse(sub.Subscription_Start_Date), 1, DateTime.Now, 1, DateTime.Now,
                    (int)cc_id, "",
                    sub.User_Name, sub.Service_Type, sub.AssetID, sub.Hostname, "", 1);
        }

        #endregion


    }
}

